#!/usr/bin/perl

use Cwd;

$baseurl = getcwd;

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

binmode(STDOUT, ':utf8');

open (STDERR, ">errors_Locations.txt");
open (STDOUT, ">prints_Locations.txt");

#NOTE: IN ORDER TO USE THIS SCRIPT, YOU MUST FIRST GET A PRINT OUT OF THE LOCATIONS OR BRANCHES CODES

#THEN YOU MUST "EXPORT" INFORMATION FROM CATALOG
#for instance:

#RECORD #(CHECKIN)   SCODE1  SCODE2  LOCATION    RLOC    CREATED(CHECKIN)    UPDATED(CHECKIN)    CKIN INFO   CHECK-IN    ERM LINK    LIB. HAS(CHECKIN)   CALL #(CHECKIN) RECORD #(BIBLIO)    CALL #(BIBLIO)

#THE RESULTING TEXT FILE NEEDS TO BE LOADED INTO ./locations/ 

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

$folder_name = 'locations';

$where = $baseurl . '/' . $folder_name;

##START: GET ilocs AND iloc_names OF LOCATIONS IN _locations TABLES

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

$this_table = $db_local_tables_hash{'locations_table'};

        $querystring = "SELECT iloc, iloc_name
                        FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

        while (@row = $query->fetchrow_array) {
            $already_loaded{$row[0]}->{'iloc_name'} = $row[1];
        }

        $query->finish();

##END: GET ilocs AND iloc_names OF LOCATIONS IN _locations TABLE

@current_time = localtime;

###START: UPDATE $this_table TABLE LINES

while (defined($next_file = <$where/*.p>)) {

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

    while(<IN>) {

        if($_ =~ /^[0-9]{1,4} > /) {

        #THIS STARTS WHERE THE NEW LOCATIONS ARE LOADED

        s/^[0-9]* > //;

        $this_iloc = $institution . "\t" . substr($_, 0, 5);
        chomp($this_iloc_name = substr($_, 10));
        $this_iloc_name =~ s/^\s+|\s+$//;

            if($already_loaded{$this_iloc}) {

#                print $this_iloc .  " | " . $this_iloc_name . "\n";

                if($this_iloc_name ne $already_loaded{$this_iloc}->{'iloc_name'}) {

#                    print "current name: " . $this_iloc_name . " is different than \nolder name: " . $already_loaded{$this_iloc}->{'iloc_name'} . "\n";

                    $querystring = "UPDATE $this_table
                                    set iloc_name = ?, 
                                        previous_iloc_name = ?,
                                        to_be_reviewed = 'yes',
                                        iloc_status = '-'
                                    WHERE iloc = ?";

#print $querystring . " | " . $this_iloc_name . " -- " . $already_loaded{$this_iloc}->{'iloc_name'} . " -- " . $this_iloc. "\n";

$changed_names .= $this_iloc . ":\n\tfrom this:\t'" . $already_loaded{$this_iloc}->{'iloc_name'} . "'\n\tto this:\t'" . $this_iloc_name. "'\n\n";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);

                    $query->execute($this_iloc_name, $already_loaded{$this_iloc}->{'iloc_name'}, $this_iloc)
                        or die("couldn't execute statement: ". $dbh->errstr);

                    $query->finish();

                }

delete $already_loaded{$this_iloc};

            } else {

                    $querystring = "INSERT into $this_table 
                                    (iloc_name, iloc, to_be_reviewed, iloc_status) 
                                    VALUES (?, ?, 'yes', '-')";

#print $querystring . " | " . $this_iloc_name . " -- " . $this_iloc. "\n";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);

                    $query->execute($this_iloc_name, $this_iloc)
                        or die("couldn't execute statement: ". $dbh->errstr);

                    $query->finish();


$new_locations .= $this_iloc .  " | " . $this_iloc_name . "\n";
                print "NEW LOCATION: " . $this_iloc .  " | " . $this_iloc_name . "\n";
            }

        }

    }

}


$that_table = $db_local_tables_hash{'items_record_table'};

                 $querystring = "SELECT iloc from $this_table";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);

                    $query->execute()
                        or die("couldn't execute statement: ". $dbh->errstr);

                    while ( @row = $query->fetchrow_array ) {
                        push (@all_ilocs, $row[0]); ## iloc
                    }

                    $query->finish();

        foreach (@all_ilocs) {

                 $querystring = "SELECT * from $that_table
                                WHERE item_iloc = ?";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);

                    $query->execute($_)
                        or die("couldn't execute statement: ". $dbh->errstr);

                    $iloc_rows = $query->rows();

                    $query->finish();

                    $querystring = "UPDATE $this_table
                                    set numberofitems = ?
                                    WHERE iloc = ?";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);

                    $query->execute($iloc_rows, $_)
                        or die("couldn't execute statement: ". $dbh->errstr);

                    $query->finish();

        }

$added_loc = "mu3ug\tkngl ";
$already_loaded{$added_loc}->{'iloc_name'} = 'experimenting again';

foreach $keys (sort (keys(%already_loaded))) {

                 $querystring = "SELECT numberofitems, status from $this_table
                                WHERE iloc = ?";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);

                    $query->execute($keys)
                        or die("couldn't execute statement: ". $dbh->errstr);

                    while ( @row = $query->fetchrow_array ) {
                        $iloc_counts = $row[0];
                        $iloc_status = $row[1];
                    }

                    $query->finish();

    if($iloc_status ne 'x') {
        $unlisted_ilocs .= $keys . " -- " . $already_loaded{$keys}->{'iloc_name'} . "(" . $iloc_counts . " item records)\n";
    }
}

if($changed_names =~ /\w/ || $new_locations =~ /\w/ || $unlisted_ilocs =~ /\w/) {
open(MAIL, "|/usr/sbin/sendmail -t");

## Mail Header
print MAIL "To: $email_address\n";
print MAIL "From: root@localhost\n";
print MAIL "Subject: locations_load.pl has finished $start -- $end\n\n";
## Mail Body
print MAIL "locations_load.pl has finished\n Starttime: $start \tEndtime: $end\n\n$script_times\nPlease review the following records in $db_local_tables_hash{'locations_table'}\n";

if($changed_names ne '') {
print MAIL "The iloc_name has changed for these records. Check to make sure no other changes have occured:\n\n
$changed_names\n\n";
}

if($new_locations ne '') {
print MAIL "These ilocs are new and need to have other information added, including iloc, bloc, iloc_name, bloc_name, campus_name, swordloc, field_049, labelloc, Ascending, status, itype, icode1, imessage, callnumber, explanation and maps:\n\n
$new_locations\n";
}

if($unlisted_ilocs ne '') {
print MAIL "These ilocs are no longer in the III catalog and should be reviewed:\n\n
$unlisted_ilocs\n\n
";
}

close(MAIL);

}

####continue WORKING on THIS at THIS point...


####START: CREATE CHECKIN RECORDS
#
#        $query_insert{'location_record'} = $institution . 'c' . substr($fields[0], 1, 6);
#        $query_insert{'scode1'} = $fields[1];
#        $query_insert{'location_status'} = $fields[2]; #scode2
#        @locations = split(",", $fields[3]);
#        $query_insert{'rloc'} = $fields[4];
#        $query_insert{'location_created'} = $fields[5];
#        $query_insert{'location_updated'} = $fields[6];
#        $query_insert{'ckin_info'} = $fields[7];
#        $query_insert{'erm_link'} = $fields[8];
#        ($query_insert{'lib_has'} = $fields[9]) =~ s/,/, /g;
#        ($query_insert{'location_callnumber'} = $fields[10]) =~ s/^[\s]*//;
#
#                foreach $date_field( location_created, location_updated ) {
#                    if($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{4})/) {
#                        $query_insert{$date_field} = $3 . "-" . $1 . "-" . $2;
#                    } elsif($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{2})/) {
#                        $query_insert{$date_field} = "19" . $3 . "-" . $1 . "-" . $2;
#                    }
#                }
#
#                @local_control_numbers = ();
#                @callnumbers = ();
#
#                $diff = $#fields - 11;
#
#                $limit = $diff/2;
#
#                for($a = 0; $a < $limit; $a++) {
#                    $b = 11 + $a;
#                    $c = $b + 1 + $limit;
#                    
#                    push(@local_control_numbers, $institution . substr($fields[$b], 0, 8));
#
#if ($query_insert{'ckin_info'} ne '' && $query_insert{'location_status'} !~ /[ns]/) {
#$location_record_digits = substr($fields[0], 1, 6);
#$query_insert{'latest_received'} = 'http://holmes.lib.muohio.edu/search~S9?/.' . $fields[$b] . '/.' . $fields[$b] . '/1,1,1,B/crC' . $location_record_digits . '&FF=&1,0,';
#}
#                    $bib_callnumber = $fields[$c];
#                    chomp($bib_callnumber);
#                    $bib_callnumber =~ s/^[\s]*//;
#                    if($query_insert{'location_callnumber'} eq '') {
#                        push(@callnumbers, $bib_callnumber);
#                    } else {
#                        push(@callnumbers, $query_insert{'location_callnumber'});
#                    }
#
#                }
#
####END: CREATE CHECKIN RECORDS
#
#####START: PRINT VALUES TO BATCH FILES
#
#    foreach $this_cloc (@locations) {
#
#        $query_insert{'cloc'} = $institution . '\t' . $this_cloc;
#
#
#        for($a = 0; $a < $limit; $a++) {
#
#        $query_insert{'local_control_number'} = $local_control_numbers[$a];
#        $query_insert{'location_callnumber'} = $callnumbers[$a];
#
#        @keys = keys %query_insert;
#        @values = values %query_insert;
#
#            $mysql_keys = join(", ", @keys);
#            $mysql_values = join("\t", @values);
#            $mysql_values =~ s/\\\t/\\ \t/g;
#
#            print $batch_loader $mysql_values . "\n";
#
#        }
#
#####END: PRINT VALUES TO BATCH FILES
#
#}
#
#                undef($mysql_values);
#                undef(%query_insert);
#                undef(@local_control_numbers);
#                undef(@callnumbers);
#
#        }
#    
#    }
#
#    close(IN) || "cannot close $next_file: $!";
#
#unlink($next_file);
#
#    $batch_loader->autoflush(1);
#    close($batch_loader, 2) || "cannot close $batch_loader: $!";
#
#}
#
#$locations_keys_textfiles = $baseurl . '/' . $folder_name . '_textfiles/locations.key';
#
#$locations_keys_loader = IO::File->new("> $locations_keys_textfiles") || die "cannot open $locations_keys_textfiles for writing: $!\n";
#binmode( $locations_keys_loader, ':utf8' );
#flock($locations_keys_loader, 2);
#
#    print $locations_keys_loader $mysql_keys . "\n";
#
#        $locations_keys_loader->autoflush(1);
#        close($locations_keys_loader, 2) || "cannot close $locations_keys_loader: $!";
#
#($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
#$end_of_file_creation_time = "$HOUR:$MIN:$SEC";
#
#use DBI;
#
## connection for the select
#$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;
#
#@current_time = localtime;
#
#$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";
#
#print "last_update_time: " . $last_update_time . "\n";
#
#
#if ($ARGV[0] eq 'CREATING') {
#    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'locations_record_table'}");
#    $this_table = $db_local_tables_hash{'locations_record_table'};
#} else {
#    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'locations_record_temp_table'}");
#    $this_table = $db_local_tables_hash{'locations_record_temp_table'};
#}
#
#$where = $baseurl . '/' . $folder_name . '_textfiles';
#
######START: INITIAL TABLE LOAD
#
#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " DISABLE KEYS");
#}
#
#while (defined($next_file = <$where/*.out>)) {
#
#    $sth2 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $this_table FIELDS TERMINATED BY '\t' (" . $mysql_keys . ")") || print "intial load died in local\n";
#
#unlink($next_file);
#
#}
#
#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " ENABLE KEYS");
#}
#
#
######END: INITIAL TABLE LOAD
#
#######START: UPDATE ACTUAL CHECKIN TABLE WITH DATA IN TEMP TABLE
#
#if ($ARGV[0] ne 'CREATING') {
#
#$this_table = $db_local_tables_hash{'locations_record_table'};
#$that_table = $db_local_tables_hash{'locations_record_temp_table'};
#
#@update_strings = ();
#
#    foreach $field_name (@keys) {
#        push(@update_strings, $this_table . '.' . $field_name . ' = ' . $that_table . '.' . $field_name);
#    }
#
#    $temp_updates = join(", ", @update_strings);
#
#        $querystring = "UPDATE $this_table, $that_table
#            SET $temp_updates
#            WHERE $this_table.location_record = $that_table.location_record
#            AND $this_table.local_control_number = $that_table.local_control_number";
#
#        $query = $dbh->prepare($querystring)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute()
#            or print "couldn't execute statement: $querystring";
#
#        $query->finish();
#
#        $querystring = "DELETE $that_table.*
#            FROM $that_table
#            LEFT JOIN $this_table
#            ON $that_table.location_record = $this_table.location_record
#            WHERE $that_table.location_record = $this_table.location_record
#            AND $that_table.local_control_number = $this_table.local_control_number";
#
#        $query = $dbh->prepare($querystring)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute()
#            or print "couldn't execute statement: $querystring";
#
#        $query->finish();
#
#        $querystring = "INSERT $this_table
#            ($mysql_keys) 
#            SELECT $mysql_keys from $that_table";
#
#        $query = $dbh->prepare($querystring)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute()
#            or print "couldn't execute statement: $querystring";
#
#        $query->finish();
#            
#}
#
#######END: UPDATE ACTUAL ITEM TABLE WITH DATA IN TEMP TABLE
#
########START: UPDATE $that_table TABLE WITH universal_identifiers
#
#$this_table = $db_local_tables_hash{'bibs_record_table'};
#$that_table = $db_local_tables_hash{'locations_record_table'};
#
#$update_string = "UPDATE $that_table, $this_table
#set $that_table.universal_identifier = $this_table.universal_identifier
#WHERE $that_table.solr_update >= ?
#AND $that_table.local_control_number = $this_table.local_control_number";
#
#print $update_string . "\n" . $last_update_time . "\n";
#
#
#$query = $dbh->prepare($update_string)
#                    or die("couldn't prepare statement: ". $dbh->errstr);
#
#                $query->execute($last_update_time)
#                    or die("couldn't execute statement: ". $dbh->errstr);
#
#                $query->finish();
#
########END: UPDATE $that_table TABLE WITH universal_identifiers
#
#
#########START: UPDATE $that_table TABLE
#
#$this_table = $db_local_tables_hash{'locations_record_table'};
#$that_table = $db_local_tables_hash{'bibs_record_table'};
#
#$update_string = "UPDATE $that_table, $this_table
#set $that_table.solr_update = NOW()
#WHERE $this_table.solr_update >= ?
#AND $that_table.local_control_number = $this_table.local_control_number";
#
#$query = $dbh->prepare($update_string)
#                    or die("couldn't prepare statement: ". $dbh->errstr);
#
#                $query->execute($last_update_time)
#                    or die("couldn't execute statement: ". $dbh->errstr);
#
#        $query->finish();
#
#########END: UPDATE $that_table TABLE
#
##########START: UPDATE $that_table TABLE
#
#$that_table = $db_local_tables_hash{'holdings_record_table'};
#
#             $update_string = "UPDATE $that_table, $this_table
#             set $that_table.lib_has = $this_table.lib_has,
#                $that_table.latest_received = $this_table.latest_received
#             WHERE $this_table.solr_update >= ?
#             AND $that_table.local_control_number = $this_table.local_control_number
#             AND $that_table.hloc = $this_table.cloc";
#
#        $query = $dbh->prepare($update_string)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute($last_update_time)
#            or die("couldn't execute statement: ". $dbh->errstr);
#
#        $query->finish();
#
##########END: UPDATE $that_table TABLE
#
###########START: CREATING FILES FOR LOAD INTO CONSORTIUM CHECKINS, HOLDINGS AND BIBS TABLES
#
#
#if ($consortium ne '') {
#
#($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
#$start_external_update_time = "$HOUR:$MIN:$SEC";
#
#foreach $table_name (location) {
#
#$this_table = $db_local_tables_hash{$table_name . 's_record_table'};
#
#$recordcount = 0;
#
#    $querystring = "SHOW FIELDS FROM $this_table";
#
#        $query = $dbh->prepare($querystring)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute()
#            or die("couldn't execute statement: ". $dbh->errstr);
#
#    while ( @row = $query->fetchrow_array ) {
#        push (@fields_array, $row[0]); ## field name
#    }
#
#$query->finish();
#
#pop(@fields_array);
#
#$fields_array_string = join(", ", @fields_array);
#
#$consortium_keys_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium' . $table_name . 's.key';
#
#$consortium_keys_loader = IO::File->new("> $consortium_keys_textfiles") || die "cannot open $consortium_keys_textfiles for writing: $!\n";
#binmode( $consortium_keys_loader, ':utf8' );
#flock($consortium_keys_loader, 2);
#
#    print $consortium_keys_loader $fields_array_string . "\n";
#
#        $consortium_keys_loader->autoflush(1);
#        close($consortium_keys_loader, 2) || "cannot close $consortium_keys_loader: $!";
#
#if ($ARGV[0] eq 'CREATING') {
#    $IN_string = "'n', 's', 'z', 'x'";
#} else {
#    $IN_string = "'n', 's', 'z'";
#}
#
#$limit_start = 0;
#$rownumber = 1;
#$status = $table_name . '_status';
#while($rownumber > 0) {
#
#        $querystring = "SELECT * from $this_table
#            WHERE solr_update >= ?
#            AND $status NOT IN ($IN_string)
#            LIMIT ?, 10000";
#
#        $query = $dbh->prepare($querystring)
#            or die("couldn't prepare statement: ". $dbh->errstr);
#
#        $query->execute($last_update_time, $limit_start)
#            or die("couldn't execute statement: ". $dbh->errstr);
#
#$limit_start += 10000;
#$rownumber = $query->rows();
#
#while(@query_array = $query->fetchrow_array()) {
#    $int = $recordcount / 10000;
#
#    if ($int =~ m/^\d+$/) {
#
#        if($recordcount > 0) {
#        $consortium_batch_loader->autoflush(1);
#        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
#        }
#
#$consortium_batch_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium_from_3' . $folder_name  . '_' . $table_name . $recordcount .  '.txt';
#
#$consortium_batch_loader = IO::File->new("> $consortium_batch_textfiles") || die "cannot open $consortium_batch_textfiles for writing: $!\n";
#binmode( $consortium_batch_loader, ':utf8' );
#flock($consortium_batch_loader, 2);
#
#    }
#
#$recordcount++;
#
#pop(@query_array);
#
#foreach(@query_array) {
#    s/\t/\\t/g;
#}
#
#    $query_array_string = join("\t", @query_array);
#    $query_array_string =~ s/\\\t/\\ \t/g;
#    $query_array_string =~ s/\n/\\n/g;
#
#    print $consortium_batch_loader $query_array_string . "\n";
#    $line_count++;
#    }
#
#}
#
#$query->finish();
#
#if($recordcount > 0) {
#        $consortium_batch_loader->autoflush(1);
#        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
#}
#
#undef(@fields_array);
#
#}
#
##########END: CREATING FILES FOR LOAD INTO CONSORTIUM CHECKINS, HOLDINGS AND BIBS TABLES
#
#($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
#$end_external_update_time = "$HOUR:$MIN:$SEC";
#
#}
#
#$dbh->disconnect;
#
#($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
#$end_time = "$HOUR:$MIN:$SEC";
#
#print "Start time: " . $start_time . "\nEnd of file creation time: " . $end_of_file_creation_time . "\nEnd time: " . $end_time . "\n";
#

#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

}

